﻿using System;
using System.Data;
using System.Text;
using System.Configuration;
using System.Data.SqlClient;
using PM.Code.DAL;
using System.Collections.Generic;
namespace PM.PMSys
{
    /// <summary>
    /// 数据访问类account。
    /// 张锋
    /// </summary>
    public class account
    {
        public account()
        { }
        #region  成员方法

        ///<summary>
        /// 用户登录
        ///</summary>
        public int UserLogin(string tid, string pwd)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * from Sys_Account");
            strSql.Append(" where tid=@tid and pwd=@pwd");
            SqlParameter[] parameters = {
                    new SqlParameter("@tid", SqlDbType.VarChar,100),
                   new SqlParameter("@pwd", SqlDbType.VarChar,200)};
            parameters[0].Value = tid;
            parameters[1].Value = pwd;
            SqlDataReader reader = DbHelper.ExecuteDataReaderBySql(strSql.ToString(), parameters);
            reader.Read();
            if (reader.HasRows)
            {
                if (reader["status"].ToString().Equals("1"))
                {
                    System.Web.HttpContext.Current.Session["tid"] = reader["tid"].ToString();
                    System.Web.HttpContext.Current.Session["name"] = reader["name"].ToString();
                    System.Web.HttpContext.Current.Session["sex"] = reader["sex"].ToString();
                    System.Web.HttpContext.Current.Session["depart"] = reader["depart"].ToString();
                    System.Web.HttpContext.Current.Session["usertype"] = reader["usertype"].ToString();
                    return 1;
                }
                else
                {
                    return 0;
                }
            }
            else
                return 2;
        }
        ///<summary>
        /// 检测用户密码是否正确（用户修改密码）
        ///</summary>
        ///<param name="tid">教师工号</param>
        ///<param name="password">教师原密码</param>
        ///<returns>返回1(正确)或0(密码错误)或2(无此人)或3(系统错误)</returns>
        public int CheckPwd(string tid, string password)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select pwd,salt from Sys_Account");
            strSql.Append(" where tid=@tid");
            SqlParameter[] parameters = {
                    new SqlParameter("@tid", SqlDbType.VarChar,100)};
            parameters[0].Value = tid;
            SqlDataReader reader = DbHelper.ExecuteDataReaderBySql(strSql.ToString(), parameters);
            if (reader.HasRows)
            {
                if (reader.Read())
                {
                    string pwd1 = reader[0].ToString();
                    string pwdsalt = reader[1].ToString();
                    reader.Close();
                    string pwd2 = PM.Code.Web.safety.EncodePassword(password, pwdsalt);
                    if (String.Equals(pwd1, pwd2))
                        return 1;
                    else
                        return 0;
                }
                else
                    return 3;
            }
            else
                return 2;
        }

        ///<summary>
        /// 检测用户名是否存在
        ///</summary>
        public int CheckUser(string tid)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select tid from Sys_Account");
            strSql.Append(" where tid=@tid");
            SqlParameter[] parameters = {
                    new SqlParameter("@tid", SqlDbType.VarChar,100)};
            parameters[0].Value = tid;
            SqlDataReader reader = DbHelper.ExecuteDataReaderBySql(strSql.ToString(), parameters);
            reader.Read();
            if (reader.HasRows)
            {
                reader.Close();
                return 1;
            }
            else
                return 0;
        }

        /// <summary>
        /// 增加一条数据
        /// </summary>
        public int Add(PM.Model.Sys_Account model)
        {
            SqlParameter[] parameters = {
					new SqlParameter("@tid", SqlDbType.VarChar,100),
					new SqlParameter("@name", SqlDbType.VarChar,50),
					new SqlParameter("@pwd", SqlDbType.VarChar,200),
					new SqlParameter("@salt", SqlDbType.VarChar,200),
					new SqlParameter("@field", SqlDbType.VarChar,100),
					new SqlParameter("@sex", SqlDbType.VarChar,2),
					new SqlParameter("@bg", SqlDbType.VarChar,100),
					new SqlParameter("@title", SqlDbType.VarChar,50),
					new SqlParameter("@depart", SqlDbType.VarChar,100),
					new SqlParameter("@telephone", SqlDbType.VarChar,50),
					new SqlParameter("@cellphone", SqlDbType.VarChar,50),
					new SqlParameter("@email", SqlDbType.VarChar,100),
					new SqlParameter("@regdatetime", SqlDbType.DateTime),
					new SqlParameter("@status", SqlDbType.TinyInt,1),
					new SqlParameter("@intro", SqlDbType.VarChar,200),
					new SqlParameter("@usertype", SqlDbType.TinyInt,1)};
            parameters[0].Value = model.tid;
            parameters[1].Value = model.name;
            parameters[2].Value = model.pwd;
            parameters[3].Value = model.salt;
            parameters[4].Value = model.field;
            parameters[5].Value = model.sex;
            parameters[6].Value = model.bg;
            parameters[7].Value = model.title;
            parameters[8].Value = model.depart;
            parameters[9].Value = model.telephone;
            parameters[10].Value = model.cellphone;
            parameters[11].Value = model.email;
            parameters[12].Value = model.regdatetime;
            parameters[13].Value = model.status;
            parameters[14].Value = model.intro;
            parameters[15].Value = model.usertype;
            return PM.Code.DAL.DbHelper.RunProcedure1("Pdmis_AccountAdd", parameters);
        }

        /// <summary>
        /// 修改一个账户信息
        /// </summary>
        /// <param name="model">账户实体类</param>
        ///<returns>返回影响行数</returns>
        public int UpdateAccount(PM.Model.Sys_Account model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update Sys_Account set ");
            strSql.Append("name=@name,");
            strSql.Append("field=@field,");
            strSql.Append("sex=@sex,");
            strSql.Append("bg=@bg,");
            strSql.Append("title=@title,");
            strSql.Append("depart=@depart,");
            strSql.Append("telephone=@telephone,");
            strSql.Append("cellphone=@cellphone,");
            strSql.Append("email=@email,");
            strSql.Append("intro=@intro ");
            strSql.Append(" where tid=@tid ");
            SqlParameter[] parameters = {
					new SqlParameter("@tid", SqlDbType.VarChar,100),
					new SqlParameter("@name", SqlDbType.VarChar,50),
					new SqlParameter("@field", SqlDbType.VarChar,100),
					new SqlParameter("@sex", SqlDbType.VarChar,2),
					new SqlParameter("@bg", SqlDbType.VarChar,100),
					new SqlParameter("@title", SqlDbType.VarChar,50),
					new SqlParameter("@depart", SqlDbType.VarChar,100),
					new SqlParameter("@telephone", SqlDbType.VarChar,50),
					new SqlParameter("@cellphone", SqlDbType.VarChar,50),
					new SqlParameter("@email", SqlDbType.VarChar,100),
					new SqlParameter("@intro", SqlDbType.VarChar,200)};
            parameters[0].Value = model.tid;
            parameters[1].Value = model.name;
            parameters[2].Value = model.field;
            parameters[3].Value = model.sex;
            parameters[4].Value = model.bg;
            parameters[5].Value = model.title;
            parameters[6].Value = model.depart;
            parameters[7].Value = model.telephone;
            parameters[8].Value = model.cellphone;
            parameters[9].Value = model.email;
            parameters[10].Value = model.intro;

            return Int32.Parse(PM.Code.DAL.DbHelper.ExecuteSql(strSql.ToString(), parameters).ToString());
        }

        /// <summary>
        /// 删除一个账户，删除与教师一切相关的数据（事务处理）
        /// </summary>
        /// <param name="tid">教师工号</param>
        /// <returns>返回受影响行数</returns>
        public int Delete_Account(string tid)
        {
            try
            {
                List<string> sqlstr = new List<string>();
                StringBuilder sqlstr1 = new StringBuilder("delete from Basic_UserMail where sender='").Append(tid).Append("'");
                StringBuilder sqlstr2 = new StringBuilder("delete from Bus_Project where pmanager='").Append(tid).Append("'");
                StringBuilder sqlstr3 = new StringBuilder("delete from Sys_Account where tid='").Append(tid).Append("'");
                StringBuilder sqlstr4 = new StringBuilder("update Sys_Guide set incharge='否',inchargeid=null,inchargename=null where inchargeid='").Append(tid).Append("'");
                StringBuilder sqlstr5 = new StringBuilder("update Dict_College set incharge='否',inchargeid=null,inchargename=null where inchargeid='").Append(tid).Append("'");
                sqlstr.Add(sqlstr1.ToString());
                sqlstr.Add(sqlstr2.ToString());
                sqlstr.Add(sqlstr3.ToString());
                sqlstr.Add(sqlstr4.ToString());
                sqlstr.Add(sqlstr5.ToString());
                return PM.Code.DAL.DbHelper.ExecuteSqlTran(sqlstr);
            }
            catch (System.Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        ///用户启用状态修改
        /// <param name="id">ID</param>
        /// <param name="ispass">是否通过审核，通过为true</param>
        /// </summary>
        public int StatusChange(int id, bool ispass)
        {
            StringBuilder strSql = new StringBuilder();
            if (ispass == true)
            {
                strSql.Append("update Sys_Account set status=1");
            }
            else
            {
                strSql.Append("update Sys_Account set status=2");
            }
            strSql.Append(" where id=@id ");
            SqlParameter[] parameters = {
					new SqlParameter("@id", SqlDbType.Int,4)};
            parameters[0].Value = id;
            return PM.Code.DAL.DbHelper.ExecuteSql(strSql.ToString(), parameters);
        }

        /// <summary>
        ///重置用户密码为123456
        /// </summary>
        /// <param name="tid">用户id</param>
        /// <param name="password">用户密码123456加密</param>
        /// <param name="salt">用户新的salt</param>
        public int ResetPwd(string tid, string password, string salt)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update Sys_Account set pwd=@pwd,");
            strSql.Append("salt=@salt");
            strSql.Append(" where tid=@tid ");
            SqlParameter[] parameters = {
					new SqlParameter("@tid", SqlDbType.VarChar,100),
                                        new SqlParameter("@pwd",SqlDbType.VarChar,200),
                                        new SqlParameter("@salt",SqlDbType.VarChar,200)};
            parameters[0].Value = tid;
            parameters[1].Value = password;
            parameters[2].Value = salt;
            return PM.Code.DAL.DbHelper.ExecuteSql(strSql.ToString(), parameters);
        }

        /// <summary>
        /// 得到一个账户实体Model
        /// </summary>
        /// <param name="id">教师工号</param>
        ///<returns>实体类</returns>
        public PM.Model.Sys_Account GetModel(string id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  tid,name,pwd,salt,field,sex,bg,title,depart,telephone,cellphone,email,regdatetime,status,intro,usertype from Sys_Account ");
            strSql.Append(" where tid=@id and status=1");
            SqlParameter[] parameters = {
					new SqlParameter("@id", SqlDbType.NVarChar,100)};
            parameters[0].Value = id;

            PM.Model.Sys_Account model = new PM.Model.Sys_Account();
            DataTable dt = PM.Code.DAL.DbHelper.ExecuteDataTableBySql(strSql.ToString(), parameters);
            if (dt.Rows.Count > 0)
            {
                model.tid = dt.Rows[0]["tid"].ToString();
                model.name = dt.Rows[0]["name"].ToString();
                model.pwd = dt.Rows[0]["pwd"].ToString();
                model.salt = dt.Rows[0]["salt"].ToString();
                model.field = dt.Rows[0]["field"].ToString();
                model.sex = dt.Rows[0]["sex"].ToString();
                model.bg = dt.Rows[0]["bg"].ToString();
                model.title = dt.Rows[0]["title"].ToString();
                model.depart = dt.Rows[0]["depart"].ToString();
                model.telephone = dt.Rows[0]["telephone"].ToString();
                model.cellphone = dt.Rows[0]["cellphone"].ToString();
                model.email = dt.Rows[0]["email"].ToString();
                if (dt.Rows[0]["regdatetime"].ToString() != "")
                {
                    model.regdatetime = DateTime.Parse(dt.Rows[0]["regdatetime"].ToString());
                }
                if (dt.Rows[0]["status"].ToString() != "")
                {
                    model.status = int.Parse(dt.Rows[0]["status"].ToString());
                }
                model.intro = dt.Rows[0]["intro"].ToString();
                if (dt.Rows[0]["usertype"].ToString() != "")
                {
                    model.usertype = int.Parse(dt.Rows[0]["usertype"].ToString());
                }
                return model;
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 登录的方法（参数给定之前需要去前后空格，返回值为空时，登录成功，否则返回错误信息）
        /// </summary>
        /// <param name="UserId">用户名</param>
        /// <param name="Password">密码</param>
        /// <returns></returns>
        public string Login(string UserId, string Password)
        {
            if (string.IsNullOrEmpty(UserId) || string.IsNullOrEmpty(Password))
                return "用户名或密码不正确，无法让您登录！";
            if (UserId.Length < 3 || UserId.Length > 20 || Password.Length < 3)
            {
                return "用户名或密码不正确，无法让您登录！";
            }
            try
            {
                string PasswordSalt = GetPasswordSalt(UserId);
                if (PasswordSalt.Length < 5)
                {
                    return "您登录的帐号信息不存在！";
                }
                SqlDataReader dr = null;
                try
                {
                    SqlParameter[] parameters = {
                    new SqlParameter("@tid", SqlDbType.NVarChar,50),
                    new SqlParameter("@password", SqlDbType.NVarChar,200),
                    new SqlParameter("@ip", SqlDbType.NVarChar,15),
                    new SqlParameter("@browser", SqlDbType.NVarChar,50)};
                    parameters[0].Value = UserId;
                    parameters[1].Value = PM.Code.Web.safety.EncodePassword(Password, PasswordSalt);
                    parameters[2].Value = System.Web.HttpContext.Current.Request.UserHostAddress;
                    try
                    {
                        parameters[3].Value = System.Web.HttpContext.Current.Request.Browser.Type;
                    }
                    catch
                    {
                        parameters[4].Value = "无法获取";
                    }
                    //使用登录存储过程，在存储过程中实现加入登录日志，修改最后登录信息等
                    dr = (SqlDataReader)PM.Code.DAL.DbHelper.RunProcedure("Pdmis_Login", parameters);
                    if (dr != null)
                    {
                        if (dr.FieldCount > 2 && dr.Read())
                        {
                            if (dr["State"].ToString() == "无效")
                            {
                                return "对不起！您的帐号未开通或被停用！";
                            }
                            else
                            {
                                System.Web.HttpContext.Current.Session["tid"] = dr[0].ToString();//教师工号
                                System.Web.HttpContext.Current.Session["name"] = dr[1].ToString();//教师姓名
                                System.Web.HttpContext.Current.Session["sex"] = dr[2].ToString();//性别
                                System.Web.HttpContext.Current.Session["depart"] = dr[3].ToString();//学院
                                System.Web.HttpContext.Current.Session["usertype"] = dr[4].ToString();//帐户类型编号
                                //AddUser();
                                return String.Empty;
                            }
                        }
                    }
                }
                finally
                {
                    if (dr != null)
                    {
                        dr.Close();
                        dr.Dispose();
                        dr = null;
                    }
                }
            }
            catch (Exception ex)
            {
                //Error("用户登录：" + ex.ToString());
            }
            return "登陆失败，请重试！";
        }
        /// <summary>
        /// 获取密码的salt值
        /// </summary>
        /// <param name="userid">用户id</param>
        /// <returns>返回salt</returns>
        private string GetPasswordSalt(string userid)
        {
            try
            {
                SqlParameter[] parameters = {
					new SqlParameter("@id", SqlDbType.NVarChar,100)};
                parameters[0].Value = userid;
                object RetVal = PM.Code.DAL.DbHelper.GetSingle("select salt from Sys_Account where tid=@userid", parameters);
                if (RetVal != null)
                {
                    return RetVal.ToString();
                }
            }
            catch (Exception ex)
            {
                //Error("取密码权值：" + ex.ToString());
            }
            return string.Empty;
        }
        /// <summary>
        /// 添加用户列表,同时也用于刷新用户在线状态
        /// </summary>
        //public void AddUser()
        //{
        //    DataTable userTable = (DataTable)System.Web.HttpContext.Current.Application["UserOnLine"];
        //    DataRow curRow = userTable.Rows.Find(new object[] { System.Web.HttpContext.Current.Session["UserName"] });
        //    if (curRow != null)
        //    {
        //        this.GetDataRowFromHttpApp(ref curRow);
        //    }
        //    else
        //    {
        //        DataRow newRow = userTable.NewRow();
        //        this.GetDataRowFromHttpApp(ref newRow);
        //        userTable.Rows.Add(newRow);
        //    }
        //    userTable.AcceptChanges();
        //    System.Web.HttpContext.Current.Application.Lock();
        //    System.Web.HttpContext.Current.Application["UserOnLine"] = userTable;
        //    System.Web.HttpContext.Current.Application.UnLock();
        //}
        #endregion  成员方法
    }
}

